﻿Imports System.Data.SqlClient
Public Class FrmNhapSoBoDiaChinh
    Public m_tennhap As String
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHuyen.DisplayMember = "T_huyen"
        ComboBoxHuyen.ValueMember = "maH"
        ComboBoxHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxKho.DisplayMember = "TenKho"
        ComboBoxKho.ValueMember = "maKhoID"
        ComboBoxKho.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxKe.DisplayMember = "TenKe"
        ComboBoxKe.ValueMember = "maKeID"
        ComboBoxKe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDMHienTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMHienTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHienTrang.DisplayMember = "TenHienTrang"
        ComboBoxHienTrang.ValueMember = "MaHienTrang"
        ComboBoxHienTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        ComboBoxLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        ComboBoxLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiSoBo()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMSoBo", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxLoaiSoBo.DisplayMember = "TenSoBo"
        ComboBoxLoaiSoBo.ValueMember = "maSoBo"
        ComboBoxLoaiSoBo.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NhapSoBo()
        Try
            Dim NgayNhap As String
            NgayNhap = String.Format("{0:MM/dd/yyyy}", DateTimePickerNgayNhap.Value)
            Dim tennhap As String
            tennhap = ModuleTenDangNhap.m_tennhap
            Dim Sql As String = ""
            Sql = " INSERT INTO SoBoDiaChinh " &
            " (Maso" &
           ", Quyenso" &
           ", NamLap" &
           ", Ghichu" &
           ", CoSoPhapLy" &
           ", MahuyenID" &
           ", MaXaID" &
           ", MaKhoID" &
           ", MaKeID" &
           ", MaHienTrangID" &
           ", MaLoaiTaiLieuID" &
           ", MaLoaiSoBoID" &
           ", NgayNhap" &
           ", NgaySua" &
           ", NguoiNhap" &
           ", MaTinhTrangID)" &
           " VALUES " &
            " ( '" & TextBoxMaSo.Text & "', " &
            " '" & TextBoxQuyenSo.Text & "', " &
            " '" & TextBoxNamThanhLap.Text & "', " &
            " N'" & TextBoxGhiChu.Text & "', " &
            " N'" & TextBoxCoSoPhapLy.Text & "', " &
            " '" & ComboBoxHuyen.SelectedValue.ToString & "', " &
            " '" & ComboBoxXa.SelectedValue.ToString & "', " &
            " '" & ComboBoxKho.SelectedValue.ToString & "', " &
            " '" & ComboBoxKe.SelectedValue.ToString & "', " &
            " '" & ComboBoxHienTrang.SelectedValue.ToString & "', " &
            " '" & ComboBoxLoaiTaiLieu.SelectedValue.ToString & "', " &
            " '" & ComboBoxLoaiSoBo.SelectedValue.ToString & "', " &
            " '" & NgayNhap & "' ," &
            " " & Format(Now.ToOADate() - 2) & ", " &
            "  N'" & tennhap & "', " &
            " '1' )"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()

            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            MsgBox("Thêm thông tin sổ bộ thành công!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub FrmNhapSoDiaChinh_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' to mau cac dong trong datagridlietkebando doi mau khac nhau
        DataGridViewSoBoDiaChinh.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewSoBoDiaChinh.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        NapDonViHuyen()
        NapKho()
        NapKe()
        NapDMHienTrang()
        NapLoaiTaiLieu()
        NapLoaiSoBo()
        TextBoxMaSo.Text = checkSoThuTu() + 1
        ButtonLuu.Enabled = False
    End Sub
    Private Sub ComboBoxHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxHuyen.SelectedIndexChanged
        ' load ten huyen
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & ComboBoxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Public Function checkSoThuTu()
        ob_cnn.Open()
        Dim sqlCom As New SqlCommand("select max(maso) from SoBoDiaChinh ", ob_cnn)
        Dim max As Int32 = Convert.ToInt32(sqlCom.ExecuteScalar())
        ob_cnn.Close()
        Return max
    End Function
    Public Function checkMaso(ByVal ID As String) As Integer
        ob_cnn.Open()
        Dim sqlCom As New SqlCommand("select count(maso) from SoBoDiaChinh where MaSo='" & ID & "' ", ob_cnn)
        Dim count As Int32 = Convert.ToInt16(sqlCom.ExecuteScalar())
        ob_cnn.Close()
        Return count
    End Function
    Public Sub NapDuLieuNhapVaodataGird()
        DataGridViewSoBoDiaChinh.Rows.Add(DateTimePickerNgayNhap.Text, TextBoxQuyenSo.Text, TextBoxNamThanhLap.Text, TextBoxGhiChu.Text, TextBoxCoSoPhapLy.Text)
    End Sub
    Private Sub ButtonLuu_Click(sender As Object, e As EventArgs) Handles ButtonLuu.Click
        If TextBoxQuyenSo.Text = "" Then
            MessageBox.Show("Chưa nhập thứ tự quyển sổ")
            Return
        End If
        If (checkMaso(TextBoxMaSo.Text) > 0) Then
            MsgBox(" Mã số đã trùng, bạn kiểm tra lại ")
            Return
        End If

        If TextBoxNamThanhLap.Text = "" Then
            MessageBox.Show("Chưa nhập năm thành lập sổ ")
            Return
        End If
        If TextBoxCoSoPhapLy.Text = "" Then
            MessageBox.Show("Chưa nhập cơ sở pháp lý thành lập")
            Return
        Else
            NapDuLieuNhapVaodataGird()
            NhapSoBo()
            TextBoxMaSo.Text = checkSoThuTu() + 1
            ob_cnn.Close()
        End If

    End Sub
    Private Sub DataGridViewSoBoDiaChinh_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewSoBoDiaChinh.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewSoBoDiaChinh.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewSoBoDiaChinh.RowHeadersWidth < Size.Width + 20 Then DataGridViewSoBoDiaChinh.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub

    Private Sub ButtonNhap_Click(sender As Object, e As EventArgs) Handles ButtonNhap.Click
        ButtonLuu.Enabled = True
        ButtonNhap.Enabled = False
    End Sub

    Private Sub DataGridViewSoBoDiaChinh_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewSoBoDiaChinh.CellContentClick

    End Sub
End Class